const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || "43.143.253.188",
  port: parseInt(process.env.DB_PORT || "3306", 10),
  user: process.env.DB_USER || "nextjs",
  password: process.env.DB_PASS || "yEAiRE2jcxTyRBy5",
  database: process.env.DB_NAME || "nextjs",
  charset: 'utf8mb4',
  timezone: '+08:00',
  acquireTimeout: 60000,
  timeout: 60000,
};

// 读取副将基础数据JSON文件
function loadDeputyGeneralData() {
  try {
    const jsonPath = path.join(__dirname, 'output', 'websocket_data_2025-08-27T09-19-03-318Z.json');
    const jsonData = fs.readFileSync(jsonPath, 'utf8');
    return JSON.parse(jsonData);
  } catch (error) {
    console.error('❌ 读取副将基础数据JSON文件失败:', error.message);
    throw error;
  }
}

// 解析副将数据
function parseDeputyGeneralData(deputyData) {
  const deputy = {
    name: deputyData.name || '',
    head_img: null, // 头像图片，从名称生成
    description: deputyData.descr || '',
    star_level: deputyData.s || 1,
    ap_min: 0,
    ap_max: 0,
    czl_min: 0,
    czl_max: 0,
    hp_min: 0,
    hp_max: 0,
    mp_min: 0,
    mp_max: 0,
    sp_min: 0,
    sp_max: 0,
    sex: 1 // 默认男性，可以根据名称推断
  };

  // 解析攻击力范围
  if (deputyData.ap) {
    const apRange = parseRange(deputyData.ap);
    deputy.ap_min = apRange.min;
    deputy.ap_max = apRange.max;
  }

  // 解析成长率范围
  if (deputyData.czl) {
    const czlRange = parseRange(deputyData.czl);
    deputy.czl_min = czlRange.min;
    deputy.czl_max = czlRange.max;
  }

  // 解析血量范围
  if (deputyData.hp) {
    const hpRange = parseRange(deputyData.hp);
    deputy.hp_min = hpRange.min;
    deputy.hp_max = hpRange.max;
  }

  // 解析魔法范围
  if (deputyData.mp) {
    const mpRange = parseRange(deputyData.mp);
    deputy.mp_min = mpRange.min;
    deputy.mp_max = mpRange.max;
  }

  // 解析速度范围
  if (deputyData.sp) {
    const spRange = parseRange(deputyData.sp);
    deputy.sp_min = spRange.min;
    deputy.sp_max = spRange.max;
  }

  // 生成头像图片名称
  deputy.head_img = generateHeadImg(deputy.name);

  // 推断性别
  deputy.sex = inferSex(deputy.name);

  return deputy;
}

// 解析范围字符串（如 "63-73" 或 "1.1-1.3"）
function parseRange(rangeStr) {
  if (!rangeStr) return { min: 0, max: 0 };
  
  const parts = rangeStr.split('-');
  if (parts.length === 2) {
    return {
      min: parseFloat(parts[0].trim()),
      max: parseFloat(parts[1].trim())
    };
  }
  
  // 如果只有一个值，min和max都设为该值
  const singleValue = parseFloat(rangeStr.trim());
  return { min: singleValue, max: singleValue };
}

// 生成头像图片名称
function generateHeadImg(name) {
  if (!name) return null;
  
  // 简单的头像名称生成规则
  const nameMap = {
    '南华仙人': 'nanhua_xianren',
    '童渊': 'tong_yuan',
    '孙寒华': 'sun_hanhua',
    '水镜先生': 'shuijing_xiansheng',
    '甘宁': 'gan_ning',
    '关羽': 'guan_yu',
    '张飞': 'zhang_fei',
    '赵云': 'zhao_yun',
    '马超': 'ma_chao',
    '黄忠': 'huang_zhong',
    '诸葛亮': 'zhuge_liang',
    '庞统': 'pang_tong',
    '周瑜': 'zhou_yu',
    '陆逊': 'lu_xun',
    '曹操': 'cao_cao',
    '司马懿': 'sima_yi',
    '荀彧': 'xun_yu',
    '郭嘉': 'guo_jia',
    '吕布': 'lu_bu',
    '貂蝉': 'diao_chan',
    '大乔': 'da_qiao',
    '小乔': 'xiao_qiao',
    '甄姬': 'zhen_ji',
    '蔡文姬': 'cai_wenji',
    '黄月英': 'huang_yueying',
    '孙尚香': 'sun_shangxiang',
    '祝融': 'zhu_rong',
    '孟获': 'meng_huo',
    '华佗': 'hua_tuo',
    '左慈': 'zuo_ci',
    '于吉': 'yu_ji',
    '张角': 'zhang_jiao',
    '张宝': 'zhang_bao',
    '张梁': 'zhang_liang'
  };

  return nameMap[name] || name.replace(/[^\u4e00-\u9fa5a-zA-Z0-9]/g, '_').toLowerCase();
}

// 推断性别
function inferSex(name) {
  if (!name) return 1; // 默认男性
  
  // 已知的女性角色
  const femaleNames = [
    '孙寒华', '貂蝉', '大乔', '小乔', '甄姬', '蔡文姬', '黄月英', 
    '孙尚香', '祝融', '马云禄', '关银屏', '张星彩', '王异', '步练师',
    '辛宪英', '王元姬', '张春华', '卞夫人', '邹氏', '樊氏', '糜夫人',
    '甘夫人', '吴国太', '孙夫人', '严氏', '杜氏', '刘氏', '何氏'
  ];
  
  return femaleNames.includes(name) ? 0 : 1; // 0:女, 1:男
}

async function importDeputyGeneralBase() {
  let connection;
  
  try {
    console.log('🚀 开始导入副将基础数据...');
    
    // 连接数据库
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功！');
    
    // 检查表是否存在
    const [tables] = await connection.execute("SHOW TABLES LIKE 'deputy_general_base'");
    if (tables.length === 0) {
      console.log('❌ deputy_general_base 表不存在，请先创建表结构');
      return;
    }
    
    console.log('✅ deputy_general_base 表存在');
    
    // 读取JSON数据
    console.log('📖 读取副将基础数据JSON文件...');
    const jsonData = loadDeputyGeneralData();
    console.log(`📊 找到 ${jsonData.length} 个副将数据`);
    
    // 清空现有数据
    console.log('🗑️ 清空现有副将基础数据...');
    await connection.execute('DELETE FROM deputy_general_base');
    console.log('✅ 现有数据已清空');
    
    // 去重处理
    console.log('🔄 开始去重处理...');
    const uniqueDeputies = new Map();
    
    for (let i = 0; i < jsonData.length; i++) {
      const deputyData = jsonData[i];
      const deputy = parseDeputyGeneralData(deputyData);
      
      // 使用名称作为唯一键，如果已存在则跳过
      if (!uniqueDeputies.has(deputy.name)) {
        uniqueDeputies.set(deputy.name, deputy);
      } else {
        console.log(`⚠️ 跳过重复副将: ${deputy.name}`);
      }
    }
    
    console.log(`📊 去重后共有 ${uniqueDeputies.size} 个唯一副将`);
    
    // 导入新数据
    console.log('📥 开始导入副将基础数据...');
    
    const insertSql = `
      INSERT INTO deputy_general_base (
        name, head_img, description, star_level,
        ap_min, ap_max, czl_min, czl_max,
        hp_min, hp_max, mp_min, mp_max,
        sp_min, sp_max, sex, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `;
    
    let successCount = 0;
    let errorCount = 0;
    
    for (const [name, deputy] of uniqueDeputies) {
      try {
        const values = [
          deputy.name,
          deputy.head_img,
          deputy.description,
          deputy.star_level,
          deputy.ap_min,
          deputy.ap_max,
          deputy.czl_min,
          deputy.czl_max,
          deputy.hp_min,
          deputy.hp_max,
          deputy.mp_min,
          deputy.mp_max,
          deputy.sp_min,
          deputy.sp_max,
          deputy.sex
        ];
        
        await connection.execute(insertSql, values);
        console.log(`✅ 导入副将: ${deputy.name} (星级:${deputy.star_level}, 性别:${deputy.sex === 0 ? '女' : '男'})`);
        successCount++;
        
      } catch (error) {
        console.error(`❌ 导入副将失败: ${name}`, error.message);
        errorCount++;
      }
    }
    
    console.log('\n📊 导入结果统计:');
    console.log(`✅ 成功导入: ${successCount} 条记录`);
    console.log(`❌ 导入失败: ${errorCount} 条记录`);
    console.log(`📈 总计处理: ${jsonData.length} 条记录`);
    
    // 验证导入结果
    console.log('\n🔍 验证导入结果...');
    const [results] = await connection.execute('SELECT COUNT(*) as count FROM deputy_general_base');
    console.log(`📊 数据库中现有副将数量: ${results[0].count}`);
    
    // 统计星级分布
    const [starStats] = await connection.execute(`
      SELECT star_level, COUNT(*) as count 
      FROM deputy_general_base 
      GROUP BY star_level 
      ORDER BY star_level
    `);
    
    console.log('\n📈 副将星级分布:');
    starStats.forEach(stat => {
      console.log(`  ${stat.star_level}星: ${stat.count} 个副将`);
    });
    
    // 统计性别分布
    const [sexStats] = await connection.execute(`
      SELECT sex, COUNT(*) as count 
      FROM deputy_general_base 
      GROUP BY sex 
      ORDER BY sex
    `);
    
    console.log('\n📈 副将性别分布:');
    sexStats.forEach(stat => {
      const sexText = stat.sex === 0 ? '女性' : '男性';
      console.log(`  ${sexText}: ${stat.count} 个副将`);
    });
    
    // 显示部分副将信息
    console.log('\n📋 部分副将信息:');
    const [sampleDeputies] = await connection.execute(`
      SELECT name, star_level, ap_min, ap_max, hp_min, hp_max, sex
      FROM deputy_general_base 
      ORDER BY star_level DESC, ap_max DESC
      LIMIT 10
    `);
    
    sampleDeputies.forEach(deputy => {
      const sexText = deputy.sex === 0 ? '女' : '男';
      console.log(`  ${deputy.name} (${deputy.star_level}星, ${sexText}) - 攻击:${deputy.ap_min}-${deputy.ap_max}, 血量:${deputy.hp_min}-${deputy.hp_max}`);
    });
    
    console.log('\n🎉 副将基础数据导入完成！');
    
  } catch (error) {
    console.error('❌ 导入过程中发生错误:', error);
    throw error;
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

// 执行导入
if (require.main === module) {
  importDeputyGeneralBase()
    .then(() => {
      console.log('✅ 脚本执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('❌ 脚本执行失败:', error);
      process.exit(1);
    });
}

module.exports = { importDeputyGeneralBase, parseDeputyGeneralData, parseRange };
